package com.dongdongqiang.common.utils;

import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlExportParameterVisitor;
import com.alibaba.druid.sql.visitor.ExportParameterVisitor;
import com.alibaba.druid.util.JdbcConstants;

import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * sql去参数
 * Created by zzb on 17-3-9.
 */
public class SQLUtils {
    /**sql操作类型*/
    public static enum SQL_TYPE {
        SELECT("DQL"),UPDATE("DML"),DELETE("DML"),INSERT("DML"),SET("DCL"),COMMIT("DCL"),ROLLBACK("DCL")
        ,CREATE("DDL"),DROP("DDL"),ALTER("DDL"),CALL("DML"),EXPLAIN("DML"),ILLEGAL("ILLEGAL") ;
        private String operateType;

        public static SQL_TYPE getByName(String name){
            for (SQL_TYPE sql_type : SQL_TYPE.values()) {
                if(sql_type.name() .equals(name)){
                    return sql_type;
                }
            }
            return ILLEGAL;
        }

        SQL_TYPE(String operateType) {
            this.operateType = operateType;
        }
        public String getOperateType() {
            return operateType;
        }

        public void setOperateType(String operateType) {
            this.operateType = operateType;
        }
    }

    private static final Pattern mysqlHeadFilter = Pattern.compile("(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER|CALL|EXPLAIN).*");
    /**获取去参数话的SQL*/
    public static String getSqlWithoutParam(String sql){
        try {
            List<SQLStatement> stmtLista = com.alibaba.druid.sql.SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
            StringBuilder out = new StringBuilder();
            ExportParameterVisitor visitor = new MySqlExportParameterVisitor(out);
            for (SQLStatement stmt : stmtLista) {
                stmt.accept(visitor);
            }
            return out.toString();
        } catch (Exception e) {
            return sql;
        }
    }
    /**验证SQL是否有效*/
    public static boolean sqlIsValid(String sql){
        try {
            List<SQLStatement> stmtLista = com.alibaba.druid.sql.SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
            StringBuilder out = new StringBuilder();
            ExportParameterVisitor visitor = new MySqlExportParameterVisitor(out);
            for (SQLStatement stmt : stmtLista) {
                stmt.accept(visitor);
            }
            return true;
        } catch (Exception e) {
            return false;
        }
    }

    /**去除Mysql包头*/
    public static String filterMysqlHead(String sql){
        StringBuilder sqlUpperCase = new StringBuilder(sql.toUpperCase());
        Matcher matcher = mysqlHeadFilter.matcher(sqlUpperCase);
        if(matcher.find()){
            String operateKey = matcher.group(0);
            if(operateKey.startsWith("EXPLAIN")) return "";
            return sql.substring(sqlUpperCase.indexOf(operateKey));

        }
        return "";
    }
    /**通过SQL获取sql操作类型，包括：
     * SELECT("DQL"),UPDATE("DML"),DELETE("DML"),INSERT("DML"),SET("DCL"),COMMIT("DCL"),ROLLBACK("DCL")
     ,CREATE("DDL"),DROP("DDL"),ALTER("DDL"),CALL("DML"),EXPLAIN("DML"),ILLEGAL("ILLEGAL")
     * */
    public static SQL_TYPE getSqlType(String sql){
        String sqlUpperCase = sql.toUpperCase();
        Matcher matcher = mysqlHeadFilter.matcher(sqlUpperCase);
        if(matcher.find()){
            String operateKey = matcher.group(0);
            return SQL_TYPE.getByName(operateKey.split(" ")[0]);
        }
        return SQL_TYPE.ILLEGAL;
    }

    public static void main(String[] args) {
        String sql1 = "..12..select * from sbtest3 limit 1,4\n";
        System.out.println(filterMysqlHead(sql1));
        SQL_TYPE sqlType = getSqlType(sql1);
        System.out.println(sqlType.getOperateType());
    }


}
